SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- DROP PROCEDURE [dbo].[sp_migra_agentes]
create PROCEDURE [dbo].[sp_migra_agentes] AS
declare @idagente int
declare @Documento int
declare @TipoDocumento int
declare @Apellido varchar(50)
declare @nombre varchar(50)
declare @Nombres varchar(50)
declare @Telefono varchar(30)
declare @Domicilio varchar(100)
declare @Localidad int
declare @LugarNacimiento int
declare @CodPostal varchar(20)
declare @Sexo int
declare @FechaNacimiento datetime
declare @CUIT varchar(20)
declare @EstadoCivil int
declare @Nacionalidad int 
declare @Email varchar(50)
declare @PercibeJubilacion varchar(1)
declare @PorcentajeRetiro int
declare @AptitudFisica varchar(1)
declare @FechaApto datetime
declare @FechaVencApto datetime --EntidadCertificadoraApto, 
declare @DedicacionExlusiva varchar(1)
declare @Vive varchar(1)
declare @FechaAlta datetime
declare @Baja varchar(1)
declare @FechaBaja datetime
declare @CausaBaja int
declare @FirmaReciboLiq varchar(1)
declare @TipoJubilacion int
declare @LegajoDePrimaria varchar(20)
declare @LegajoDeMedia varchar(20)
declare @TipoAgente int
declare @FechaHastaDocente datetime
declare @FechaHastaAdmin datetime
declare @DocenteAnios int
declare @AdminAnios int
declare @DocenteMeses int
declare @Adminmeses int
--declare @sucursal_id_sarrhh int
declare @sucursal_id_sage int
declare @agente_cuenta varchar(15)
declare @maximo int
declare @GrupoYFactorSanguineo int

DBCC CHECKIDENT ('SageNet.dbo.tb_Antiguedades',RESEED,0)

delete from sagenet..tb_ConceptosDeDesempeno
delete from sagenet..tb_cursosDictados
delete from sagenet..tb_Antecedentes
delete from sagenet..tb_TitulosDelAgente
delete from sagenet..tb_FamiliaresACargo
delete from sagenet..tb_antiguedades
delete from sagenet..tb_HistoriaClinica
delete from tb_CuentasLiquidacion
delete from tb_afiliaciones

delete from sagenet..tb_agentes


DBCC CHECKIDENT ('SageNet.dbo.tb_Agentes',RESEED,0)

SET IDENTITY_INSERT SageNet.dbo.tb_Afiliaciones  ON

 
BEGIN TRANSACTION;
--BEGIN TRY


--select a.pers_documento,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1 from rrhh_15.dbo.agente a where pers_documento = 26433497

select distinct 
		a.pers_documento,
		COALESCE((select top 1 doc.cte_tipo_documento from rrhh_15.dbo.documento as doc
                where p.pers_documento = doc.pers_documento and p.pers_sexo = doc.pers_sexo),0) as tipodoc,
		p.pers_apellido,
		p.pers_nombre,
		substring(p.pers_apellido,1,30) + ', ' + substring(p.pers_nombre,1,18) as nombres,

		(select top 1 isnull (d.domicilio_tel_cod,'') + isnull (d.domicilio_tel_nro,'')
                from rrhh_15.dbo.domicilio as d, rrhh_15.dbo.domicilio_persona as dp
                where p.pers_documento = dp.pers_documento and p.pers_sexo = dp.pers_sexo
		and d.domicilio_id = dp.domicilio_id)
		as telefono,

		(select top 1 isnull (d.domicilio_calle,'') + ' ' + isnull (d.domicilio_nro,'') +  
		' ' + isnull (d.domicilio_piso,'') + ' ' + isnull (d.domicilio_departamento,'')
                from rrhh_15.dbo.domicilio as d, rrhh_15.dbo.domicilio_persona as dp
                where p.pers_documento = dp.pers_documento and p.pers_sexo = dp.pers_sexo
		and d.domicilio_id = dp.domicilio_id)
		as domicilio,

		COALESCE((select top 1 d.tab_local_id from rrhh_15.dbo.domicilio as d, rrhh_15.dbo.domicilio_persona as dp, SageNet.dbo.tb_Localidades l
                where p.pers_documento = dp.pers_documento and p.pers_sexo = dp.pers_sexo
		and d.domicilio_id = dp.domicilio_id and d.tab_local_id = l.idLocalidad),1)
                as localidad,

		case when COALESCE(p.tab_local_id,1) = 509 then 1 else COALESCE(p.tab_local_id,1) end as Nacimiento,

		null as CodigoPostal,
		
		--(select idsexo from sagenet.dbo.tb_sexo where left(descripcion,1) = p.pers_sexo) as sexo,
		case when p.pers_sexo ='F' then 1 else 2 end as sexo,
		
		p.pers_fecha_nacimiento,
			
		substring(convert(varchar(2),a.agente_pre_cuil) + 
		         case when len(convert(varchar(8),a.pers_documento)) = 8 then convert(varchar(8),a.pers_documento) 
		         else replicate('0',8-len(a.pers_documento)) + convert(varchar(8),a.pers_documento) end +  convert(varchar(1), a.agente_suf_cuil),1,11)
		as cuit,
		case when p.tab_estado_civil_id = 9 then 4 else p.tab_estado_civil_id end as estado_civil,
		p.tab_pais_id as nacionalidad,

		(select top 1 d.domicilio_email from rrhh_15.dbo.domicilio as d, rrhh_15.dbo.domicilio_persona as dp
                where p.pers_documento = dp.pers_documento and p.pers_sexo = dp.pers_sexo
		and d.domicilio_id = dp.domicilio_id)
                as email,

		case a.agente_pension
			when 0 then null
			when 1 then 'N'
			when 2 then 'S'
		end as agente_pension,--(0 nada 1 no 2 si)
			
		a.agente_porc_retiro as PorcentajeRetiro,

		case hc.hist_clin_discapacitado
			when 1 then 'N'
			when 2 then 'S'
			else 'N'
		end as apto,--(0 nada,1 no,2 si)
		
		case 
			when hc.hist_clin_factor_rh = 0 and hc.hist_clin_grupo_sanguineo = 0 then 1
			when hc.hist_clin_factor_rh = 0 and hc.hist_clin_grupo_sanguineo = 1 then 1
			when hc.hist_clin_factor_rh = 0 and hc.hist_clin_grupo_sanguineo = 2 then 1
			when hc.hist_clin_factor_rh = 1 AND hc.hist_clin_grupo_sanguineo = 0 then 1
			when hc.hist_clin_factor_rh = 1 AND hc.hist_clin_grupo_sanguineo = 1 then 1
			when hc.hist_clin_factor_rh = 1 AND hc.hist_clin_grupo_sanguineo = 2 then 2
			when hc.hist_clin_factor_rh = 2 AND hc.hist_clin_grupo_sanguineo = 0 then 1
			when hc.hist_clin_factor_rh = 2 AND hc.hist_clin_grupo_sanguineo = 1 then 3
			when hc.hist_clin_factor_rh = 2 AND hc.hist_clin_grupo_sanguineo = 2 then 4
			when hc.hist_clin_factor_rh = 3 AND hc.hist_clin_grupo_sanguineo = 0 then 1
			when hc.hist_clin_factor_rh = 3 AND hc.hist_clin_grupo_sanguineo = 1 then 5
			when hc.hist_clin_factor_rh = 3 AND hc.hist_clin_grupo_sanguineo = 2 then 6
			when hc.hist_clin_factor_rh = 4 AND hc.hist_clin_grupo_sanguineo = 0 then 1
			when hc.hist_clin_factor_rh = 4 AND hc.hist_clin_grupo_sanguineo = 1 then 7
			when hc.hist_clin_factor_rh = 4 AND hc.hist_clin_grupo_sanguineo = 2 then 8
		end as GrupoYFactorSanguineo,--(0 nada,1 no,2 si)
		
		null as FechaApto,
		null as FechaVencApto ,
		--a.organismo_id, se saca porque no tenemos los organismos cargados del sarrhh
		a.agente_dedicacion_exclu,


		case CAST (  p.pers_vive AS VARCHAR(1) )
			when '1' then 'N'
			when '2' then 'S'
			else Null	
		end as pers_vive,--(2 vale S, 1 vale N)

		a.agente_fecha_alta,
		case when a.agente_fecha_fallece is not null then 'S' else 'N' end as Baja, --'N' as Baja,-- (S/N)	
		case when a.agente_fecha_fallece is null then a.agente_fecha_jubilacion else a.agente_fecha_fallece end as FechaBaja, --null as  FechaBaja,
		case 
			 when a.agente_fecha_fallece is not null then 3 --Fallecimiento
			 when a.agente_fecha_jubilacion is not null then 5 --Jubilacion
			 else null 
	    end as CausaBaja,   	
		null as	FirmaReciboLiq,
		null as TipoJubilacion,-- (tb_TiposDeJubilacion)
		null as LegajoDePrimaria,
		null as LegajoDeMedia,
		1 as tipoagente,
		a.agente_antig_fecha_docente,
		a.agente_antig_fecha_admin,
		a.agente_antig_docente_anos,
		a.agente_antig_admin_anos,
		a.agente_antig_docente_meses,
		a.agente_antig_admin_meses,
		case a.sucursal_id when 0 then 9999 else a.sucursal_id end as sucursal_id,
		a.agente_cuenta 
		into #tmp_agentes
		
from
		rrhh_15.dbo.persona as p,
		rrhh_15.dbo.agente as a
		left outer join rrhh_15.dbo.historia_clinica as hc on (a.pers_documento = hc.pers_documento)
		--left outer join sagenet.mig._cnv_pk_tb_CuentasBancarias as cnv on (cnv.sucursal_id = a.sucursal_id)
		--rrhh_15.dbo.domicilio as d, 
		--rrhh_15.dbo.domicilio_persona as dp --,
		--rrhh_15.dbo.documento as doc
where
		p.pers_documento = a.pers_documento and 
		p.pers_sexo = a.pers_sexo 
		--and p.pers_documento = 22751673
		--p.pers_documento in (12796217,12796226,12796231)
		--p.pers_documento not in (select documento from SageNet.dbo.tb_Agentes)
		--a.pers_documento *= hc.pers_documento --and 
		--p.pers_documento = dp.pers_documento and
		--d.domicilio_id = dp.domicilio_id --and
		--dp.pers_documento =doc.pers_documento 
		AND P.PERS_DOCUMENTO <> 99999999
--order by a.pers_documento
  
declare XXXprueba Cursor for
SELECT * FROM #tmp_agentes
open xxxprueba
fetch next from xxxprueba into @Documento, @TipoDocumento, @Apellido, @nombre, @Nombres, @Telefono, @Domicilio, @Localidad,
				@LugarNacimiento, @CodPostal, @Sexo, @FechaNacimiento, @CUIT, @EstadoCivil, @Nacionalidad, @Email,
				@PercibeJubilacion, @PorcentajeRetiro, @AptitudFisica,@GrupoYFactorSanguineo, @FechaApto, @FechaVencApto,--EntidadCertificadoraApto, 
				@DedicacionExlusiva, @Vive, @FechaAlta, @Baja, @FechaBaja, @CausaBaja, @FirmaReciboLiq,@TipoJubilacion,
				@LegajoDePrimaria, @LegajoDeMedia, @TipoAgente,@FechaHastaDocente,@FechaHastaAdmin,
				@DocenteAnios, @AdminAnios, @DocenteMeses, @Adminmeses, 
				@sucursal_id_sage, @agente_cuenta

while @@fetch_status = 0
  begin
    set @idagente=null
	SELECT @idagente = idagente FROM Sagenet.dbo.tb_Agentes WHERE documento = @Documento AND Sexo = @Sexo
	--SELECT @Documento, 'Documento' , @idagente
	IF  @idagente IS NULL
	BEGIN
		--SELECT @Documento, 'Documento'  
		--Insertar tb_agentes
		INSERT INTO SageNEt.dbo.tb_Agentes (Documento, TipoDocumento, Apellido,nombre,
					Nombres, Telefono, Domicilio, Localidad, LugarNacimiento, CodPostal,
					Sexo, FechaNacimiento, CUIT, EstadoCivil, Nacionalidad, Email,
					PercibeJubilacion, PorcentajeRetiro, AptitudFisica, FechaApto,
					FechaVencApto, --EntidadCertificadoraApto, 
					DedicacionExlusiva, Vive,
		        		FechaAlta, Baja, FechaBaja, CausaBaja, FirmaReciboLiq, TipoJubilacion,
					LegajoDePrimaria, LegajoDeMedia, TipoAgente)
					VALUES (@Documento, @TipoDocumento, @Apellido, @nombre, @Nombres, @Telefono, @Domicilio, @Localidad ,
					@LugarNacimiento, @CodPostal, @Sexo, @FechaNacimiento, @CUIT, @EstadoCivil, @Nacionalidad, @Email,
					@PercibeJubilacion, @PorcentajeRetiro, @AptitudFisica, @FechaApto, @FechaVencApto,--EntidadCertificadoraApto, 
					@DedicacionExlusiva, @Vive, @FechaAlta, @Baja, @FechaBaja, @CausaBaja, @FirmaReciboLiq,@TipoJubilacion,
					@LegajoDePrimaria, @LegajoDeMedia, @TipoAgente)

		--set @idAgente=@@identity
		set @idAgente=ident_current ('tb_agentes')
		--SELECT 'Nuevo ' + convert(varchar,@idagente)
	END	
	ELSE
	BEGIN
		--SELECT @Documento, 'Documento'  
			UPDATE SageNEt.dbo.tb_Agentes SET Domicilio=@Domicilio, Localidad=@Localidad ,
					LugarNacimiento=@LugarNacimiento, CodPostal=@CodPostal, Sexo=@Sexo, FechaNacimiento=@FechaNacimiento, CUIT=@CUIT, EstadoCivil=@EstadoCivil, Nacionalidad=@Nacionalidad, Email=@Email,
					PercibeJubilacion=@DedicacionExlusiva,  PorcentajeRetiro=@PorcentajeRetiro, AptitudFisica=@AptitudFisica, FechaApto=@FechaApto, FechaVencApto=@FechaVencApto,
					DedicacionExlusiva=@DedicacionExlusiva, Vive=@Vive, FechaAlta=@FechaAlta, Baja=@Baja, FechaBaja=@FechaBaja, CausaBaja=@CausaBaja, FirmaReciboLiq=@FirmaReciboLiq,TipoJubilacion=@TipoJubilacion,
					LegajoDePrimaria=@LegajoDePrimaria, LegajoDeMedia=@LegajoDeMedia, TipoAgente=@TipoAgente
			WHERE idAgente = @idagente 

			--SELECT 'Modificado ' + convert(varchar,@idagente)
	END
	
	INSERT INTO SageNet.dbo.tb_Afiliaciones(idAfiliacion,idAgente,TipoAfiliacion,NumeroAfiliado,Organismo)
		VALUES(@idAgente,@idAgente,1,@Documento,1)
	

	--Insertar tb_CuentasLiquidacion (Antes insertar tb_afiliaciones)
	INSERT INTO SageNet.dbo.tb_CuentasLiquidacion (Agente, TipoDeCuenta, NroDeCuenta, SucursalBancaria,ALaOrdenDe, Afiliacion)
		VALUES(@idAgente,1, coalesce(@agente_cuenta,'Sin_Cta'), coalesce(@sucursal_id_sage,9999),1, @idAgente)
		--select @idAgente,1, coalesce(@agente_cuenta,'Sin_Cta'), coalesce(@sucursal_id_sage,1),1, @idAgente
		

	IF @FechaHastaDocente IS NOT NULL 
	BEGIN
		--Insertar tb_antiguedades
		--Antiguedad Docente:
		INSERT INTO SageNet.dbo.tb_antiguedades (idAgente, ConceptoAntiguedad, FechaHasta, FechaDesde,Anios, Meses,Dias,Manual,Recalcular,FechaCalculo)
			VALUES(@idAgente,13, @FechaHastaDocente, @FechaHastaDocente, isnull(@DocenteAnios,0), isnull(@DocenteMeses,0),0,'N','N',@FechaHastaDocente)
	END

	IF @FechaHastaAdmin IS NOT NULL 
	BEGIN

		--Antiguedad Administrativa:
		INSERT INTO SageNet.dbo.tb_antiguedades (idAgente, ConceptoAntiguedad, FechaHasta, FechaDesde,Anios, Meses,Dias,Manual,Recalcular,FechaCalculo)
			VALUES(@idAgente,14, @FechaHastaAdmin, @FechaHastaAdmin, isnull(@AdminAnios,0), isnull(@Adminmeses,0),0,'N','N',@FechaHastaAdmin)
	END 
	
	INSERT INTO SageNet.dbo.tb_HistoriaClinica (Agente, GrupoYFactorSanguineo,EstadoGralSalud,Apto,AlcanceApto,TieneAlteracion)
		VALUES(@idAgente,@GrupoYFactorSanguineo,2,'S',1,'N') 

	fetch next from xxxprueba into @Documento, @TipoDocumento, @Apellido, @nombre, @Nombres, @Telefono, @Domicilio, @Localidad,
				@LugarNacimiento, @CodPostal, @Sexo, @FechaNacimiento, @CUIT, @EstadoCivil, @Nacionalidad, @Email,
				@PercibeJubilacion, @PorcentajeRetiro, @AptitudFisica,@GrupoYFactorSanguineo, @FechaApto, @FechaVencApto,--EntidadCertificadoraApto, 
				@DedicacionExlusiva, @Vive, @FechaAlta, @Baja, @FechaBaja, @CausaBaja, @FirmaReciboLiq,@TipoJubilacion,
				@LegajoDePrimaria, @LegajoDeMedia, @TipoAgente,@FechaHastaDocente,@FechaHastaAdmin,
				@DocenteAnios, @AdminAnios, @DocenteMeses, @Adminmeses, 
				@sucursal_id_sage, @agente_cuenta
  end
close xxxprueba 
deallocate xxxprueba

--END TRY
--BEGIN CATCH
	
--    IF @@TRANCOUNT > 0
--	begin
--        ROLLBACK TRANSACTION;
--		select 'rollback' + convert(varchar,@idagente)
--	end
--END CATCH;

--IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;


SET IDENTITY_INSERT SageNet.dbo.tb_Afiliaciones  OFF

select @maximo = max(idAgente) from tb_Agentes

DBCC CHECKIDENT ('SageNet.dbo.tb_Agentes',RESEED,@maximo)

-- rollback tran

--select * from SageNet.dbo.tb_agentes

--DBCC CHECKDB (sagenet)

--HACER SCRIPT DE LA TABLA tb_CuentasLiquidacion y volver a crearla (DROP Y CREATE)
GO
